﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DBManager.ProductUserDAL
{
    public class ProductUDAL
    {
        public static int CountProductByCompanyAndZone(string ZoneID,string CompanyID)
        {
            int kq = 0;
            string sql = @"
			SELECT count(*) as TongBanGhi	 
			FROM Products 
			WHERE Status=1 
            and CompanyID=" + CompanyID + @"
            and ZoneIdOther='" + ZoneID + ",'";
            DataTable dt=DBUtil.SelectTable(sql);
            if(dt.Rows[0]["TongBanGhi"].ToString()!="0")
            {
                kq=int.Parse(dt.Rows[0]["TongBanGhi"].ToString());
            }
            return kq;
        }
        public static DataTable GetListProductByZone(string PageSize, string PageIndex, string ZoneID, string Search, string CompanyID)
        {
            int IndexSize = int.Parse(PageSize) * int.Parse(PageIndex);
            string sql = @"SELECT  * 
		    FROM 
		    ( 
			SELECT TOP(" + IndexSize + @") 
				id,
				Title,
				Sapo,
				Avatar,
				[Url],	
                ProductCode,
                price,
                sale,
                EndDateNew,
                TypeSale,
				ROW_NUMBER() OVER (ORDER BY id DESC ) Row		 
			FROM Products 
			WHERE Status=1 
            and title like N'%" + Search + @"%' 
            and CompanyID=" + CompanyID + @"
            and (ZoneId = " + comm.to_sqlnumber(ZoneID) + " or ZoneIdOther like N'%" + ZoneID + @",%') 
			and DistributionDate <= GETDATE() 
		) News 
		WHERE   ROW Between (" + comm.to_sqlnumber(PageIndex) + " -1)*" + comm.to_sqlnumber(PageSize) + " + 1 AND " + IndexSize + "";
            return DBUtil.SelectTable(sql);
        }

        public static DataTable CountGetListProductByZone(string ZoneID, string Search, string CompanyID)
        {
            string sql = @"
			SELECT count(*) as TongBanGhi	 
			FROM Products 
			WHERE Status=1 
            and title like N'%" + Search + @"%' 
            and CompanyID=" + CompanyID + @"
            and (ZoneId = " + comm.to_sqlnumber(ZoneID) + " or ZoneIdOther like N'%" + ZoneID + @",%') 
			and DistributionDate <= GETDATE() ";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetTopHomeFocus(string Top)
        {
            string sql = @"
			SELECT top "+Top+@" * 
			FROM Products 
			WHERE Status=1  
            AND isfocus=1 order by id desc";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetInfomationProduct(string ProductID)
        {
            string sql = @"
			SELECT * 
			FROM Products 
			WHERE id=" + comm.to_sqlnumber(ProductID);
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetTopHomeProduct(string Top,string ZoneID)
        {
            string sql = @"
			SELECT top " + Top + @" * 
			FROM Products 
			WHERE Status=1 and IsFocus=1 and ZoneID=" + comm.to_sqlnumber(ZoneID) + " order by id desc";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetInfomationZone(string ZoneID)
        {
            string sql = @"
			SELECT * 
			FROM ZoneProduct 
			WHERE ZoneID=" + comm.to_sqlnumber(ZoneID);
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetListZoneByParentID(string ParentID)
        {
            string sql = @"SELECT * FROM ZoneProduct WHERE status=1 and ParentID = " + comm.to_sqlnumber(ParentID);
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetZoneByCompanyID(string CompanyID)
        {
            string sql = @"SELECT Products.ZoneIdOther,  Products.CompanyID,  Companies.NameCp ,
                        (select title from ZoneProduct where (convert(varchar(4),ZoneId)+',')=Products.ZoneIdOther ) as title 
                        ,(select parentid from zoneproduct where ZoneId=CONVERT(int, replace(Products.ZoneIdOther,',','') )) as ZoneID
                        FROM Products INNER JOIN   
                        Companies ON Products.CompanyID = Companies.CompanyID   
                        WHERE (Products.CompanyID = " + comm.to_sqlnumber(CompanyID) + @") and Products.status=1 and Companies.Status = 1
                        GROUP BY Products.ZoneIdOther, Products.CompanyID, Companies.NameCp order by CONVERT(int, replace(Products.ZoneIdOther,',','') ) asc ";
            return DBUtil.SelectTable(sql);
        }
    }
}
